package sk.stuba.fiit.repka.dp.db;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.TreeMap;

import org.apache.log4j.Logger;

import sk.stuba.fiit.repka.dp.rating.RatingManager;
import sk.stuba.fiit.repka.dp.utils.ValueComparator;

public class DBRatingManipulator implements DataManipulator{
	private static Logger logger = Logger.getLogger(DBRatingManipulator.class);
	
	private static final String INSERT_ONTOLOGY_RATING = "INSERT INTO EVALUATION " +
			"(\"ID_EXPECTATIONS\", \"VALUE\", \"UPDATED_DATE\", \"RATING_COUNT\" ) " +
			"VALUES (?, ?, ?, ?)";
	private static final String UPDATE_RATING_ACCORDING_RECOMMENDATION = "UPDATE EVALUATION " +
			"SET VALUE=?, UPDATED_DATE=?, RATING_COUNT=? " +
			"WHERE ID_EXPECTATIONS=?";
	
	private String ontoName;
	private DatabaseController dbControl;
	private int ratingExpec;
	private int ratingRecomm;
	private List<String> attributes;
	
	public DBRatingManipulator(DatabaseController dbControl, String ontoName, int ratingExpec, int ratingRecomm) {
		this.dbControl = dbControl;
		this.ontoName = ontoName;
		this.ratingExpec = ratingExpec;
		this.ratingRecomm = ratingRecomm;
	}
	
	public DBRatingManipulator(DatabaseController dbControl) {
		this.dbControl = dbControl;
	}
	
	//get recommendations according to previously inserted attributes of concrete type
	public List<String> getRecommendations(List<String> insertedAttributes, String attrType) throws SQLException {
		List<String> result = new ArrayList<String>();
		
		HashMap<Integer,Float> map = new HashMap<Integer,Float>();
        ValueComparator bvc =  new ValueComparator(map); //serves to sort map according rating values
        TreeMap<Integer, Float> sorted_map = new TreeMap(bvc);

		String querySelectRating = "SELECT VALUE FROM EVALUATION WHERE ID_EXPECTATIONS = ?";
		List<Integer> expIDs = this.getExpectationsIDs(this.getAttributesIDs(insertedAttributes));
		Iterator<Integer> iter = expIDs.iterator();
		while(iter.hasNext()) {
			Integer in = iter.next();
			PreparedStatement statement = this.dbControl.getCon().prepareStatement(querySelectRating);
			statement.setInt(1, in);
			ResultSet rs = statement.executeQuery();
			if(rs.next()) {
				map.put(in, rs.getFloat("VALUE"));
			}
			rs.close();
			statement.close();
		}
		
		sorted_map.putAll(map); //now we have sorted map according rating values
		
		String querySelectRecommendations = "SELECT DISTINCT VALUE FROM ATTRIBUTE JOIN EXPECTATIONS_ATTRIBUTES " +
				"ON ATTRIBUTE.ID=EXPECTATIONS_ATTRIBUTES.ID_ATTRIBUTE " +
				"WHERE EXPECTATIONS_ATTRIBUTES.ID_EXPECTATION = ? AND ATTRIBUTE.ID_ATTRIBUTE_TYPE = ?";
		int attrTypeID = this.getAttrTypeID(attrType);
		int counterExpectations = 0;
		for(Integer key : sorted_map.keySet()) {
			counterExpectations++;
			int limit = 0;
			PreparedStatement statement = this.dbControl.getCon().prepareStatement(querySelectRecommendations);
			statement.setInt(1, key);
			statement.setInt(2, attrTypeID);
			
			//we will work only with three best expectations
			if(counterExpectations == 1) {
				limit = 10; //take 10 recommendations
			} else if(counterExpectations == 2){
				limit = 6; //take 6 recommendations
			} else if(counterExpectations == 3) {
				limit = 4; //take 4 recommendations
			} else {
				break; //not work with next expectations
			}
			
			ResultSet rs = statement.executeQuery();
			int counterRecommendations = 0;
			while(rs.next()) {
				String value = rs.getString("VALUE");
				//check if selected attribute from DB which will be used as recommendation isnt in previously inserted attributes
				//from which we created recommendations
				boolean wasValueInsertedYet = false;
				for(String s : insertedAttributes) {
					if(s.equalsIgnoreCase(value)) {
						wasValueInsertedYet = true;
						break;
					}
				}
				
				if(!wasValueInsertedYet && counterRecommendations < limit) {
					logger.debug("Inserting recommendation: " + value + " from expectation: " + key);
					boolean isAlreadyInResult = false;
					//if value is already in result dont add it to result again
					for(String s : result) {
						if(s.equalsIgnoreCase(value)) {
							isAlreadyInResult = true;
							break;
						}
					}
					if(!isAlreadyInResult) {
						result.add(value);
						counterRecommendations++;
					}
				}
				
				if(counterRecommendations == limit) {
					break; //fill result only until recommendations are under limit
				}
			}
			rs.close();
			statement.close();
		}
		
		return result;
	}
	
	private int getAttrTypeID(String type) throws SQLException {
		String query = "SELECT * FROM ATTRIBUTE_TYPE where type_name = ?";
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
		statement.setString(1, type);
		ResultSet rs = statement.executeQuery();
		int result = -1;
		while(rs.next()) {
			result = rs.getInt("ID");
		}
		
		rs.close();
		statement.close();
		return result;
	}
	
	private int getOntologyId() throws SQLException {
		String query = "SELECT * FROM EXPECTATIONS WHERE URL=?";
		int result = -1;
		
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
		statement.setString(1, this.ontoName);
		ResultSet rs = statement.executeQuery();
		if(rs.next()) {
			result = rs.getInt("ID");
		}
		rs.close();
		statement.close();
		
		return result;
	}
	
	private List<Integer> getAttributesIDs(List<String> attributes) throws SQLException {
		List<Integer> result = new ArrayList<Integer>();
		String query = "SELECT ID FROM ATTRIBUTE WHERE VALUE=?";
		Iterator<String> iter = attributes.iterator();
		while(iter.hasNext()) {
			String value = iter.next();
			PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
			statement.setString(1, value);
			ResultSet rs = statement.executeQuery();
			if(rs.next()) {
				result.add(rs.getInt("ID"));
			}
			rs.close();
			statement.close();
		}
		return result;
	
	}
	
	//get expectations id according to attributes which were used from recommendations
	private List<Integer> getExpectationsIDs(List<Integer> attributesIDs) throws SQLException {
		List<Integer> result = new ArrayList<Integer>();
		String query = "SELECT DISTINCT ID FROM EXPECTATIONS EX JOIN EXPECTATIONS_ATTRIBUTES EA ON EX.ID=EA.ID_EXPECTATION WHERE ID_ATTRIBUTE=?";
		Iterator<Integer> iter = attributesIDs.iterator();
		while(iter.hasNext()) {
			Integer id = iter.next();
			PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
			statement.setInt(1, id);
			ResultSet rs = statement.executeQuery();
			while(rs.next()) {
				result.add(rs.getInt("ID"));
			}
			rs.close();
			statement.close();
		}
		return result;
	}
	
	@Override
	public boolean insert() throws SQLException{
		boolean result = false;
		
		this.dbControl.getCon().setAutoCommit(false);
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(INSERT_ONTOLOGY_RATING);
		statement.setInt(1, this.getOntologyId());
		statement.setInt(2, this.ratingExpec);
		java.util.Date date = new java.util.Date();
		Date sqlDate = new Date(date.getTime());
		statement.setDate(3, sqlDate);
		statement.setInt(4, 1);
		int count = statement.executeUpdate();
		this.dbControl.getCon().commit();
		statement.close();
		this.dbControl.getCon().setAutoCommit(true);
		
		if(count > 0) {
			result = true;
		}
		
		return result;
	}

	@Override
	public boolean update(List<? extends Object> toUpdate) throws SQLException {
		this.attributes = new ArrayList<String>((List<String>)toUpdate);
		String querySelectCountValue = "SELECT VALUE, RATING_COUNT FROM EVALUATION WHERE ID_EXPECTATIONS=?";
		
		Iterator<Integer> iter = this.getExpectationsIDs(this.getAttributesIDs(this.attributes)).iterator();
		int count = 0; 
		while(iter.hasNext()) {
			int id = iter.next();
			PreparedStatement statement = this.dbControl.getCon().prepareStatement(querySelectCountValue);
			statement.setInt(1, id);
			ResultSet rs = statement.executeQuery();
			if(rs.next()){
				int ratingCount = rs.getInt("RATING_COUNT") + 1;
				float newRating = RatingManager.recalculateRating(rs.getFloat("VALUE"), ratingCount, this.ratingRecomm);
				this.dbControl.getCon().setAutoCommit(false);
				PreparedStatement statementUpdate = this.dbControl.getCon().prepareStatement(UPDATE_RATING_ACCORDING_RECOMMENDATION);
				statementUpdate.setFloat(1, newRating);
				java.util.Date date = new java.util.Date();
				Date sqlDate = new Date(date.getTime());
				statementUpdate.setDate(2, sqlDate);
				
				statementUpdate.setInt(3, ratingCount);
				statementUpdate.setInt(4, id);
				
				statementUpdate.executeUpdate();
				this.dbControl.getCon().commit();
				statementUpdate.close();
				this.dbControl.getCon().setAutoCommit(true);
			}
			rs.close();
			statement.close();
		}
		
		if(count > 0) {
			return true;
		}
		return false;
	}

	@Override
	public boolean delete(String query) {
		// TODO Auto-generated method stub
		return false;
	}

}
